package com.jyh.db.dao;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

import com.jyh.config.datasource.TargetDataSource;
import com.jyh.util.Utils;

@Repository
public class BiDataStatisticsDao {
	@Autowired
	private JdbcTemplate jdbcTemplate;

	
	/**
	 * 注册人数
	 * @return
	 */
	@TargetDataSource(name = "user")
	public String getTheRegistrationUser(String time){
		String sql = "Select COUNT(1)countId from jyh_person where 1=1 ";
		if(!Utils.isNull(time)){
			sql +="and TO_DAYS(logtime)=TO_DAYS('"+time+"')";
		}
		Map<String,Object> map = jdbcTemplate.queryForMap(sql);
		if(map==null){
			return "0";
		}
		return map.get("countId")+"";
	}
	
	/**
	 * 借款笔数
	 * @param time
	 * @return
	 */
	@TargetDataSource(name = "order")
	public String getFrequencyBorrowing(String time){
		String sql = "Select COUNT(1)countId from jyh_order where 1=1 ";
		if(!Utils.isNull(time)){
			sql +="and TO_DAYS(orderTime)=TO_DAYS('"+time+"')";
		}
		Map<String,Object> map = jdbcTemplate.queryForMap(sql);
		if(map==null){
			return "0";
		}
		return map.get("countId")+"";
	}
	
	/**
	 * 放款笔数，放款金额
	 * @param time
	 * @return
	 */
	@TargetDataSource(name = "order")
	public Map<String,Object> getLoanData(String time){
		String sql = "select COUNT(1)countId,SUM(putMoney) sumMoney from jyh_order_payment where 1=1 ";
		if(!Utils.isNull(time)){
			sql +="and TO_DAYS(putTime)=TO_DAYS('"+time+"')";
		}
		return jdbcTemplate.queryForMap(sql);
	}
	
	/**
	 * 驳回笔数
	 * @param time
	 * @return
	 */
	@TargetDataSource(name = "order")
	public String getDismissNumber(String time){
		String sql = "select COUNT(1)countId from jyh_order where state=4 ";
		if(!Utils.isNull(time)){
			sql +="and TO_DAYS(operationTime)=TO_DAYS('"+time+"')";
		}
		Map<String,Object> map = jdbcTemplate.queryForMap(sql);
		if(map==null){
			return "0";
		}
		return map.get("countId")+"";
	}
	
	/**
	 * 逾期笔数，逾期总额
	 * @param time
	 * @return
	 */
	@TargetDataSource(name = "order")
	public Map<String,Object> getOverdueData(String time){
		String sql = "select COUNT(1)countId,SUM(putMoney) sumMoney from jyh_order_payment where isOverdue=1 ";
		if(!Utils.isNull(time)){
			sql +="and TO_DAYS(putTime)=TO_DAYS('"+time+"')";
		}
		return jdbcTemplate.queryForMap(sql);
	}
	
	/**
	 * 当日逾期笔数
	 * @param time
	 * @param time1
	 * @return
	 */
	@TargetDataSource(name = "order")
	public String getOnDayOrderOverdue(String time,String time1){
		String sql = "select COUNT(1)countId from jyh_order as  T1 INNER JOIN jyh_order_payment as T2 on T1.orderNO = T2.orderNO  "
				+ "where T2.isOverdue=1  and ( TO_DAYS(executeTime)>TO_DAYS('"+time1+"') or executeTime is null) and TO_DAYS(T1.orderTime)=TO_DAYS('"+time+"')";
		Map<String,Object> map = jdbcTemplate.queryForMap(sql);
		if(map==null){
			return "0";
		}
		return map.get("countId")+"";
	}
	
	/**
	 * 催回笔数
	 * @param time
	 * @return
	 */
	@TargetDataSource(name = "order")
	public String getPushBack(String time,String time1){
		String sql = "select COUNT(1)countId from jyh_order as  T1 INNER JOIN jyh_order_payment as T2 on T1.orderNO = T2.orderNO "
				+ " where isCollection=1 and TO_DAYS(executeTime)<=TO_DAYS('"+time1+"') and TO_DAYS(T1.orderTime)=TO_DAYS('"+time+"')";
		Map<String,Object> map = jdbcTemplate.queryForMap(sql);
		if(map==null){
			return "0";
		}
		return map.get("countId")+"";
	}
	
	
	
	
	//A笔订单每天催回笔数  催回笔数select * from jyh_order as  T1 INNER JOIN jyh_order_payment as T2 on T1.orderNO = T2.orderNO  where isCollection=1 and TO_DAYS(T2.executeTime) = TO_DAYS('2017-10-20') and TO_DAYS(T1.endTime)=TO_DAYS('') 
}
